package yangsw87.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import yangsw87.model.MemberVO;
import yangsw87.utils.OracleJDBC;

public class MemberDAO {
	private final String insertSQL = " insert into memberv1(mno, userid, pwd, name, gender)"
									+ " values(mbseq.nextval, ?, ?, ?, ?) ";
	private final String updateSQL = " update memberv1 set pwd=?, name=?, regdate=sysdate where mno=? ";
	private final String deleteSQL = " delete from memberv1 where mno = ? ";
	private final String selectSQL = " select mno, userid, name, regdate from memberv1 order by mno ";
	private final String selectOneSQL = " select * from memberv1 where mno=? order by mno ";
	
	
	// 회원가입 insert
	public int insertMember(MemberVO m) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, m.getUserid());
				ps.setString(2, m.getPwd());
				ps.setString(3, m.getName());
				ps.setString(4, m.getGender());
			cnt = ps.executeUpdate();
		} catch (Exception ex) {
		} finally {
			OracleJDBC.closeConn(conn, ps, null);
		}
		return cnt;
	}
	
	// 회원수정 update
	public int updateMember(MemberVO m) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt=0;
		
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(updateSQL);
				ps.setString(1, m.getPwd());
				ps.setString(2, m.getName());
				ps.setString(3, m.getMno());
			cnt = ps.executeUpdate();
				
		} catch (Exception ex) {
		} finally {
			OracleJDBC.closeConn(conn, ps, null);
		}
		return cnt;
	}
	
	// 회원삭제 delete
	public int deleteMember(String mno) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt=0;
		
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1, mno);
			cnt = ps.executeUpdate();
				
		} catch (Exception ex) {
		} finally {
			OracleJDBC.closeConn(conn, ps, null);
		}
		return cnt;
	}
	
	// 회원전체조회 select
	public ArrayList<MemberVO> selectMember() {
		ArrayList<MemberVO> list = new ArrayList<>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			while(rs.next()){
				MemberVO mvo = new MemberVO(rs.getString(1), rs.getString(2), null, rs.getString(3), 
						null, rs.getString(4));
				list.add(mvo);
			}
			
		} catch (Exception ex) {
		} finally {
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return list;
	}
		
	// 회원상세조회 selectOne
	public MemberVO selectOneMember(String mno) {
		MemberVO m = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectOneSQL);
			
			ps.setString(1, mno);
			rs = ps.executeQuery();
			
			while(rs.next()){
				m = new MemberVO(rs.getString(1), rs.getString(2), rs.getString(3),
						rs.getString(4), rs.getString(5), rs.getString(6));
			}
			
		} catch (Exception ex) {
		} finally {
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return m;
	}
}
